const express = require('express');
//引入连接池
const query = require("../util/query.js");
const JWT = require('../util/token.js');
//创建空路由器
let router = express.Router();
//=================================================
//首页模块
//==================================================
/** 前端用户接口 **/
//获取首页数据 GET /index
//返回轮播数据、推荐商品、新货商品、销售排行
router.get('/index', async (req, res) => {
  try {
    let data = {};
    let sql1 = `SELECT id,img,title,href,product_id FROM lz_index_carousel ORDER BY seq LIMIT 3`;
    let sql2 = `SELECT id,title,details,pic,price,href,product_id FROM lz_index_product WHERE seq_recommended>0 ORDER BY seq_recommended  LIMIT 6`;
    let sql3 = `SELECT id,title,details,pic,price,href,product_id FROM lz_index_product WHERE seq_new_arrival>0 ORDER BY seq_new_arrival LIMIT 6`;
    let sql4 = `SELECT id,title,details,pic,price,href,product_id FROM lz_index_product WHERE seq_top_sale>0 ORDER BY seq_top_sale LIMIT 6`;
    let sql = `${sql1} ; ${sql2} ;${sql3} ; ${sql4}`;
    //执行Query
    let result = await query(sql);

    data.carouselItems = result[0];
    data.recommendedItems = result[1];
    data.newArrialItems = result[2];
    data.topSaleItems = result[3];

    return res.send({
      code: 1,
      msg: "获取成功",
      data,
    });

  } catch (error) {
    res.send({
      code: -1,
      msg: error.message
    });
  }
});

/* 管理员admin使用的接口 */
/* 首页轮播  lz_index_carousel */
// 增加
// 更改
//===============================================
//修改/新增,如果id不空则修改，id空则新增
//POST /home/carousel
//参数 ：参见表结构 id、img（必须）、title、product_id（必须）、href;
router.post("/home/carousel", async (req, res) => {
  //验证用户Token 请求头中携带Token  req.headers.authorization=
  let user = JWT.getUser(req, res);
  if (user == null) return;

  try {
    //获取数据，验证数据,
    let carousel = req.body;
    if (!carousel.img) throw new Error("图片不能为空");
    if (!carousel.product_id) throw new Error("商品id不能为空");

    carousel.id = parseInt(carousel.id) || 0;

    //要确保carousel对象的属性和表lz_index_carousel的字段属性名一致。
    //创建sql语句，并执行
    let sql = "";
    if (carousel.id) { //如果参数id有非0值的，则更新
      sql = `UPDATE lz_index_carousel SET ? WHERE id=${carousel.id}`;
    } else { // 参数无id值，则新增记录
      sql = `INSERT INTO lz_index_carousel SET ?`;
    }

    let result = await query(sql, [carousel]);

    //根据sql操作影响的记录行数，判断是否删除成功
    if (result.affectedRows > 0) {
      if (!carousel.id) carousel.id = result.insertId; //如果是新增，设置新增的id			
      res.send({
        code: 1,
        msg: "操作成功！",
        data: carousel.id
      });
      return;
    }
    throw new Error(result.message || `操作失败`);
  } catch (error) {
    res.send({
      code: -1,
      msg: error.message
    });
  }
});

// 删除
// DELETE /home/carousel
//参数 id (图片id) 位置query
router.delete("/home/carousel", async (req, res) => {
  //验证用户Token 请求头中携带Token  req.headers.authorization=
  let user = JWT.getUser(req, res);
  if (user == null) return;

  try {
    //获取数据
    let id = req.query.id;
    id = parseInt(id);
    //验证数据
    if (!id) throw new Error("编号Id不能为空");
    //执行SQL语句
    let sql = `DELETE FROM lz_index_carousel WHERE id = ${id}`;
    let result = await query(sql);
    //根据sql操作影响的记录行数，判断是否删除成功
    if (result.affectedRows > 0) {
      res.send({
        code: 1,
        msg: `删除成功 ：id=${id}`
      });
      return;
    }
    throw new Error(result.message || `没有编号为${id}的数据`);
  } catch (error) {
    res.send({
      code: -1,
      msg: error.message
    });
  }
});

// 查询列表(数据量不多，就不作分页处理了)
// GET /carousel/list 
// 参数：无
router.get("/home/carousel/list", async (req, res) => {
  //验证用户Token 请求头中携带Token  req.headers.authorization=
  let user = JWT.getUser(req, res);
  if (user == null) return;

  try {
    //建sql语句 		
    let sql = `SELECT id,img,title,product_id,href,seq FROM lz_index_carousel ORDER BY seq `;
    console.log(sql);
    //执行Query
    let result = await query(sql);
    //处理返回数据，（返回记录数量是0也是一种成功），响应对象    
    let data = result; //   
    res.send({
      code: 1,
      msg: "获取成功",
      data
    });
  } catch (error) {
    res.send({
      code: -1,
      msg: error.message
    });
  }
});

/* 首页商品 lz_index_product*/
//===============================================
//修改/新增,如果id不空则修改，id空则新增
//POST /home/product
//参数 ：参见表结构 id、img（必须）、title、product_id（必须）、href;
router.post("/home/product", async (req, res) => {
  //验证用户Token 请求头中携带Token  req.headers.authorization=
  let user = JWT.getUser(req, res);
  if (user == null) return;

  try {
    //获取数据，验证数据,
    let homeProduct = req.body;
    if (!homeProduct.pic) throw new Error("图片不能为空");
    if (!homeProduct.product_id) throw new Error("商品id不能为空");

    homeProduct.id = parseInt(homeProduct.id) || 0;

    //要确保homeProduct对象的属性和表lz_index_product的字段属性名一致。
    //表字段id,product_id,title,details,pic,price,href,seq_recommemded,seq_new_arrival,seq_top_sale
    //创建sql语句，并执行
    let sql = "";
    if (homeProduct.id) { //如果参数id有非0值的，则更新
      sql = `UPDATE lz_index_product SET ? WHERE id=${homeProduct.id}`;
    } else { // 参数无id值，则新增记录
      sql = `INSERT INTO lz_index_product SET ?`;
    }

    let result = await query(sql, [homeProduct]);

    //根据sql操作影响的记录行数，判断是否删除成功
    if (result.affectedRows > 0) {
      if (!homeProduct.id) homeProduct.id = result.insertId; //如果是新增，设置新增的id			
      res.send({
        code: 1,
        msg: "操作成功！",
        data: homeProduct.id
      });
      return;
    }
    throw new Error(result.message || `操作失败`);
  } catch (error) {
    res.send({
      code: -1,
      msg: error.message
    });
  }
});

// 删除
//DELETE /home/product
//参数 id (图片id) 位置query
router.delete("/home/product", async (req, res) => {
  //验证用户Token 请求头中携带Token  req.headers.authorization=
  let user = JWT.getUser(req, res);
  if (user == null) return;

  try {
    //获取数据
    let id = req.query.id;
    id = parseInt(id);
    //验证数据
    if (!id) throw new Error("编号Id不能为空");
    //执行SQL语句
    let sql = `DELETE FROM lz_index_product WHERE id = ${id}`;
    let result = await query(sql);
    //根据sql操作影响的记录行数，判断是否删除成功
    if (result.affectedRows > 0) {
      res.send({
        code: 1,
        msg: `删除成功 ：id=${id}`
      });
      return;
    }
    throw new Error(result.message || `没有编号为${id}的数据`);
  } catch (error) {
    res.send({
      code: -1,
      msg: error.message
    });
  }
});

// 查询列表(数据量不多，就不作分页处理了)
// GET /home/product/list 
// 参数：无
router.get("/home/product/list", async (req, res) => {
  //验证用户Token 请求头中携带Token  req.headers.authorization=
  let user = JWT.getUser(req, res);
  if (user == null) return;

  try {
    //建sql语句 		
    let sql = `SELECT * FROM lz_index_product ORDER BY id `;
    console.log(sql);
    //执行Query
    let result = await query(sql);
    //处理返回数据，（返回记录数量是0也是一种成功），响应对象    
    let data = result; //   
    res.send({
      code: 1,
      msg: "获取成功",
      data
    });
  } catch (error) {
    res.send({
      code: -1,
      msg: error.message
    });
  }
});


/* 首页 echars */
// GET /home/echars/orderSumByFamily
// 参数：无
router.get("/home/echars/orderSumByFamily", async (req, res) => {
  //验证用户Token 请求头中携带Token  req.headers.authorization=
  let user = JWT.getUser(req, res);
  if (user == null) return;

  try {
    let {
      dtBgn,
      dtEnd
    } = req.query;
    dtBgn = dtBgn || "";
    dtEnd = dtEnd || "";
    // let dtBgn = '20220416'
    // let dtEnd = '20220417'
    //建sql语句 		
    // let sql = `SELECT family_name,count,amount FROM v_order_sum_by_family `;
    let sql = ` SELECT family_name,sum(count) as count,sum(count * price) as amount 
			FROM v_order_detail , lz_order WHERE v_order_detail.order_id = lz_order.id `;
    if (dtBgn.length > 0) {
      sql += ` AND DATE_FORMAT(lz_order.order_time,'%Y%m%d') >= '${dtBgn}' `;
    }
    if (dtEnd.length > 0) {
      sql += ` AND DATE_FORMAT(lz_order.order_time,'%Y%m%d') <= '${dtEnd}' `;
    }
    sql += ' GROUP BY family_name ';
    sql += ' ORDER BY family_id  ';

    console.log(sql);
    //执行Query
    let result = await query(sql);
    //处理返回数据，（返回记录数量是0也是一种成功），响应对象    
    let data = result; //   
    res.send({
      code: 1,
      msg: "获取成功",
      data
    });
  } catch (error) {
    res.send({
      code: -1,
      msg: error.message
    });
  }
});

// 搜索
// post /home/search

router.post("/home/search", async (req, res) => {
  //验证用户Token 请求头中携带Token  req.headers.authorization=

  try {
    //获取数据
    let title = req.body.title;
    //验证数据title
    if (!title) throw new Error("title不能为空");
    //执行SQL语句
    let sql = `select title,id FROM lz_plant WHERE title like '%${title}%'`;
    let result = await query(sql);
    let data = result;
    if (result.length > 0) {
      return res.send({
        code: 1,
        msg: "搜索成功",
        data: data,
      });
    }else{
      return res.send({
        code: 1,
        msg: "查无此商品",
        data: data,
      });
    }
  } catch (error) {
    res.send({
      code: -1,
      msg: error.message
    });
  }
});


module.exports = router;